﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
using DVSProject.Models;
using System.Text;

namespace DVSProject.Lib
{
    public class ProcedureRemarkRepo
    {
        public const string m_msg_head = "ProcedureRemark";

        public IEnumerable<ProcedureRemarkModels> GetProcedureRemark()
        {
            List<ProcedureRemarkModels> ProcedureRemarks = new List<ProcedureRemarkModels>();
            string SQLCommand = @"select tbProcedureRemark.*,TBTitleProcedure.TitleProcedureName from tbProcedureRemark LEFT JOIN TBTitleProcedure
                                  ON tbProcedureRemark.TitleProcedureId = TBTitleProcedure.TitleProcedureId
                                  where .tbProcedureRemark.Status <>'Delete'  ORDER BY  tbProcedureRemark.TitleProcedureId";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        ProcedureRemarkModels ProcedureRemark = new ProcedureRemarkModels();
                        ProcedureRemark.Id = (int)reader["Id"];
                        ProcedureRemark.TitleProcedureId = (int)reader["TitleProcedureId"];
                        ProcedureRemark.TitleProcedureName = (reader["TitleProcedureName"]!=DBNull.Value )? (string)reader["TitleProcedureName"]:"";
                        ProcedureRemark.ProcedureRemarkTh = (string)reader["ProcedureRemarkTh"];
                        ProcedureRemark.ProcedureRemarkEng = (string)reader["ProcedureRemarkEng"];
                        ProcedureRemark.Status = (string)reader["Status"];
                        ProcedureRemark.CreateBy = (string)reader["CreateBy"];
                        ProcedureRemark.CreateOn = (DateTime)reader["CreateOn"];
                        ProcedureRemark.UpdateBy = (string)reader["UpdateBy"];
                        ProcedureRemark.UpdateOn = (DateTime)reader["UpdateOn"];

                        ProcedureRemarks.Add(ProcedureRemark);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return ProcedureRemarks.ToList();
        }

        public ProcedureRemarkModels GetProcedureRemarkById(int id)
        {
            ProcedureRemarkModels ProcedureRemark = new ProcedureRemarkModels();
            string SQLCommand = @"select tbProcedureRemark.*,TBTitleProcedure.TitleProcedureName from tbProcedureRemark LEFT JOIN TBTitleProcedure
                                  ON tbProcedureRemark.TitleProcedureId = TBTitleProcedure.TitleProcedureId where tbProcedureRemark.Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        ProcedureRemark.Id = (int)reader["Id"];
                        ProcedureRemark.TitleProcedureId = (int)reader["TitleProcedureId"];
                        ProcedureRemark.TitleProcedureName = (reader["TitleProcedureName"] != DBNull.Value) ? (string)reader["TitleProcedureName"] : "";
                        ProcedureRemark.ProcedureRemarkTh = (string)reader["ProcedureRemarkTh"];
                        ProcedureRemark.ProcedureRemarkEng = (string)reader["ProcedureRemarkEng"];
                        ProcedureRemark.Status = (string)reader["Status"];
                        ProcedureRemark.CreateBy = (string)reader["CreateBy"];
                        ProcedureRemark.CreateOn = (DateTime)reader["CreateOn"];
                        ProcedureRemark.UpdateBy = (string)reader["UpdateBy"];
                        ProcedureRemark.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return ProcedureRemark;
        }

        public bool Create(ProcedureRemarkModels ProcedureRemark)
        {
            bool result = true;
            string SQLCommand = @"INSERT into tbProcedureRemark 
                                    values (@TitleProcedureId,@ProcedureRemarkTh,@ProcedureRemarkEng,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleProcedureId", ProcedureRemark.TitleProcedureId);
                    db.AddParameter("@ProcedureRemarkTh", ProcedureRemark.ProcedureRemarkTh);
                    db.AddParameter("@ProcedureRemarkEng", ProcedureRemark.ProcedureRemarkEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", "900153");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(ProcedureRemarkModels ProcedureRemark)
        {
            bool result = true;
            string SQLCommand = @"Update TBProcedureRemark 
                                    Set TitleProcedureId = @TitleProcedureId,
                                        ProcedureRemarkTh=@ProcedureRemarkTh,
                                        ProcedureRemarkEng=@ProcedureRemarkEng,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

           using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", ProcedureRemark.Id);
                    db.AddParameter("@TitleProcedureId", ProcedureRemark.TitleProcedureId);
                    db.AddParameter("@ProcedureRemarkTh", ProcedureRemark.ProcedureRemarkTh);
                    db.AddParameter("@ProcedureRemarkEng", ProcedureRemark.ProcedureRemarkEng);
                    db.AddParameter("@UserID", ProcedureRemark.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
           return result;
        }

        public bool Delete(ProcedureRemarkModels ProcedureRemark)
        {
             bool result = true;
             string SQLCommand = @"Update TBProcedureRemark 
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where Id=@Id";

             using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Id", ProcedureRemark.Id);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", ProcedureRemark.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
             return result;
        }
    }
}